﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows;
using System.Data.SQLite;
using System.IO;
using System.Windows.Input;

namespace KeyboardImageViewer
{
    public static class DatabaseUpgrades
    {
        public static void UpgradeIfNeeded(SQLiteConnection connection)
        {
            int version = DatabaseConfigAccess.GetConfigInt("Version", connection);

            if (version > Utilities.CurrentDatabaseVersion)
            {
                MessageBox.Show("This version of Keyboard Image Viewer is too old to read this image database. Please upgrade to the latest version.");
                Environment.Exit(1);
            }

            if (version < Utilities.CurrentDatabaseVersion)
            {
                using (SQLiteTransaction transaction = connection.BeginTransaction())
                {
                    if (version < 2)
                    {
                        Upgrade1To2(connection);
                    }

                    if (version < 3)
                    {
                        Upgrade2To3(connection);
                    }

                    if (version < 4)
                    {
                        Upgrade3To4(connection);
                    }

                    if (version < 5)
                    {
                        Upgrade4To5(connection);
                    }

                    if (version < 6)
                    {
                        Upgrade5To6(connection);
                    }

                    Database.ExecuteNonQuery("UPDATE settings SET value = '" + Utilities.CurrentDatabaseVersion + "' WHERE name = 'Version'", connection);

                    transaction.Commit();
                }
            }
        }

        #region Upgrade functions
        private static void Upgrade1To2(SQLiteConnection connection)
        {
            string sourceDirectory = DatabaseConfigAccess.GetConfigString("SourceDirectory", connection);
            bool useSubdirectories = DatabaseConfigAccess.GetConfigBool("UseSubdirectories", connection);

            Database.ExecuteNonQuery(
                "CREATE TABLE baseDirectories (" +
                "id INTEGER PRIMARY KEY AUTOINCREMENT, " +
                "path TEXT, " +
                "missing BOOL)", connection);

            Database.ExecuteNonQuery(
                "CREATE TABLE excludedDirectories (" +
                "id INTEGER PRIMARY KEY AUTOINCREMENT, " +
                "baseId INTEGER, " +
                "path TEXT)", connection);

            MoveSourceDirectory(sourceDirectory, useSubdirectories, connection);
        }

        private static void Upgrade2To3(SQLiteConnection connection)
        {
            InvalidateUnrecognizedPixelFormatImages(connection);

            Database.ExecuteNonQuery(
                "CREATE TABLE inputMapping (" +
                "inputCode INTEGER, " +
                "inputType INTEGER, " +
                "actionCode INTEGER)", connection);

            InputMapping.InsertDefaultsIntoMapping(connection, false);
        }

        private static void Upgrade3To4(SQLiteConnection connection)
        {
            if (!InputMapping.IsBound((int)Key.D, InputType.KeyboardShift, connection))
            {
                MappingAdder mappingAdder = new MappingAdder(connection);
                mappingAdder.AddMapping((int)Key.D, InputType.KeyboardCtrl, UserAction.ShowAllDuplicates);
            }
        }

        private static void Upgrade4To5(SQLiteConnection connection)
        {
            if (!InputMapping.IsBound((int)Key.E, InputType.Keyboard, connection))
            {
                MappingAdder mappingAdder = new MappingAdder(connection);
                mappingAdder.AddMapping(Key.E, UserAction.EditCurrentFile);
            }
        }

        private static void Upgrade5To6(SQLiteConnection connection)
        {
            Database.ExecuteNonQuery("DROP TABLE summaries", connection);

            Database.ExecuteNonQuery(
                "CREATE TABLE summaries (" +
                "imageId INTEGER, " +
                "ineligible BOOL, " +
                "lastRefreshDate DATETIME, " +
                "thumbnail BINARY, " +
                "averageVariance DOUBLE, " +
                "fractionDrasticPixelBorders DOUBLE, " +
                "hashPrefix TEXT)", connection);

            Database.ExecuteNonQuery("CREATE UNIQUE INDEX imageIdIndex ON summaries (imageId)", connection);
            Database.ExecuteNonQuery("CREATE INDEX hashPrefixIndex ON summaries (hashPrefix, ineligible)", connection);

            bool trackImageDuplicates = DatabaseConfigAccess.GetConfigBool("TrackDuplicates", connection) && DatabaseConfigAccess.GetConfigBool("TrackImageDuplicates", connection);

            if (trackImageDuplicates)
            {
                DuplicateTrackingUpgrade upgradeDialog = new DuplicateTrackingUpgrade();
                upgradeDialog.ShowDialog();

                if (upgradeDialog.DialogResult != true)
                {
                    DatabaseConfigAccess.SetConfigValue("TrackImageDuplicates", false, connection);
                }
            }
        }
        #endregion

        public static void MoveSourceDirectory(string sourceDirectory, bool useSubdirectories, SQLiteConnection connection)
        {
            SQLiteCommand insertBaseDirectory = new SQLiteCommand("INSERT INTO baseDirectories (id, path, missing) VALUES (1, ?, ?)", connection);

            SQLiteParameter basePathParam = new SQLiteParameter { Value = sourceDirectory };
            SQLiteParameter baseMissingParam = new SQLiteParameter { Value = false };
            insertBaseDirectory.Parameters.Add(basePathParam);
            insertBaseDirectory.Parameters.Add(baseMissingParam);
            insertBaseDirectory.ExecuteNonQuery();

            if (!useSubdirectories && Directory.Exists(sourceDirectory))
            {
                DirectoryInfo directoryInfo = new DirectoryInfo(sourceDirectory);
                DirectoryInfo[] subdirectories = directoryInfo.GetDirectories();

                SQLiteCommand insertExcludeDirectory = new SQLiteCommand("INSERT INTO excludedDirectories (baseId, path) VALUES (1, ?)", connection);
                SQLiteParameter excludePathParam = new SQLiteParameter();
                insertExcludeDirectory.Parameters.Add(excludePathParam);

                foreach (DirectoryInfo subdirectory in subdirectories)
                {
                    excludePathParam.Value = subdirectory.FullName.ToLowerInvariant();
                    insertExcludeDirectory.ExecuteNonQuery();
                }
            }
        }

        private static void InvalidateUnrecognizedPixelFormatImages(SQLiteConnection connection)
        {
            SQLiteCommand invalidateSummary = new SQLiteCommand("UPDATE summaries SET lastRefreshDate = ? WHERE imageId = ?", connection);
            SQLiteParameter lastRefreshParam = invalidateSummary.Parameters.Add("lastRefreshDate", System.Data.DbType.DateTime);
            SQLiteParameter imageIdParam = invalidateSummary.Parameters.Add("imageId", System.Data.DbType.Int32);

            SQLiteCommand getUnrecognizedPixelFormatImages = new SQLiteCommand("SELECT imageId, lastRefreshDate FROM summaries WHERE isCorrupted = ? AND averageVariance = 0", connection);
            SQLiteParameter isCorruptedParam = getUnrecognizedPixelFormatImages.Parameters.Add("isCorrupted", System.Data.DbType.Boolean);
            isCorruptedParam.Value = false;

            using (SQLiteDataReader reader = getUnrecognizedPixelFormatImages.ExecuteReader())
            {
                while (reader.Read())
                {
                    DateTime lastRefresh = reader.GetDateTime("lastRefreshDate");
                    lastRefresh -= TimeSpan.FromDays(1);

                    lastRefreshParam.Value = lastRefresh;
                    imageIdParam.Value = reader.GetInt32("imageId");
                    invalidateSummary.ExecuteNonQuery();
                }
            }
        }
    }
}
